package com.legleg.db;


import com.legleg.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by IntelliJ IDEA.
 * User: Administrator
 * Date: 12-4-14
 * Time: 上午2:26
 * To change this template use File | Settings | File Templates.
 */
public class Dao {
    static Logger logger = org.apache.log4j.Logger.getLogger(Dao.class);


    static List list(String sql, Class type, Object... params) {
        List list = null;
        Connection con = DBUtil.getCon();
        try {
            QueryRunner run = new QueryRunner();
            list = (List) run.query(con, sql, new BeanListHandler(type), params);
        } catch (Exception e) {
            logger.error("[sqlerror]" + sql + " params:" + paramsToString(params), e);
        } finally {
            DBUtil.closeCon(con);
        }
        return list;
    }

    static List<Map<String, Object>> list(String sql, Object... params) {
        Connection con = DBUtil.getCon();
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            QueryRunner run = new QueryRunner();
            list = (List<Map<String, Object>>) run.query(con, sql, new MapListHandler(), params);
        } catch (SQLException e) {
            logger.error("[sqlerror-list] " + sql + " params:" + paramsToString(params), e);
        } finally {
            DBUtil.closeCon(con);
        }
        return list;
    }


    static Object find(String sql, Class type, Object... params) {
        Connection con = DBUtil.getCon();
        Object o = null;
        try {
            QueryRunner run = new QueryRunner();
            ResultSetHandler h = new BeanHandler(type);
            o = run.query(con, sql, h, params);
        } catch (Exception e) {
            logger.error("[sqlerror-find] " + sql + " params:" + paramsToString(params), e);
        } finally {
            DBUtil.closeCon(con);
        }
        return o;
    }

    /**
     * insert method
     * @param sql
     * @param params
     * @return auto-increased id
     */
    static long insert(String sql, Object... params) {
        Long id = -1L;
        if (sql == null || sql.toLowerCase().indexOf("insert") < 0) return id.longValue();
        Connection con = DBUtil.getCon();
        try {
            QueryRunner run = new QueryRunner();
            int updates = run.update(con, sql, params);
            if (updates > 0) {
                id = (Long) run.query(con, "SELECT LAST_INSERT_ID()", new ScalarHandler(1));
            }
        } catch (SQLException e) {
            logger.error("[sqlerror-insert] " + sql + " params:" + paramsToString(params), e);
        } finally {
            DBUtil.closeCon(con);
        }
        return id == null ? -1 : id.longValue();
    }

    /**
     * update method
     * @param sql
     * @param params
     * @return
     */
    static int update(String sql, Object... params) {
        Connection con = DBUtil.getCon();
        int affectedRows = 0;
        try {
            QueryRunner run = new QueryRunner();
            affectedRows = run.update(con, sql, params);
        } catch (SQLException e) {
            logger.error("[sqlerror-update] " + sql + " params:" + paramsToString(params), e);
        } finally {
            DBUtil.closeCon(con);
        }
        return affectedRows;
    }

    /**
     * 批量update
     * @param sql
     * @param params
     * @return
     */
    static int[] batchUpdate(String sql, Object[][] params) {
        Connection con = DBUtil.getCon();
        int[] affectedRows = new int[0];
        try {
            QueryRunner run = new QueryRunner();
            affectedRows = run.batch(con, sql, params);
        } catch (Exception e) {
            logger.error("[sqlerror-batchUpdate] " + sql + " params:" + paramsToString(params), e);
        } finally {
            DBUtil.closeCon(con);
        }
        return affectedRows;
    }

    /**
     * 获得指定列的倄1�7
     * @param sql
     * @param columnName
     * @param params
     * @return 结果对象
     */
    static Object findBy(String sql, String columnName, Object[] params) {
        Connection con = DBUtil.getCon();
        Object object = null;
        try {
            QueryRunner run = new QueryRunner();
            object = run.query(con, sql, new ScalarHandler(columnName), params);
        } catch (Exception e) {
            logger.error("[sqlerror-findBy] " + sql + " params:" + paramsToString(params), e);
        }finally {
            DBUtil.closeCon(con);
        }
        return object;
    }

    /**
     * 查询某一条记录，并将指定列的数据转换为Object
     * @param sql sql语句
     * @param columnIndex 列索弄1�7
     * @param params 参数数组
     * @return 结果对象
     */
    static Object findBy(String sql, int columnIndex, Object... params) {
        Connection con = DBUtil.getCon();
        Object object = null;
        try {
            QueryRunner run = new QueryRunner();
            object = run.query(con, sql, new ScalarHandler(columnIndex), params);
        } catch (Exception e) {
            logger.error("[sqlerror-findBy] " + sql + " params:" + paramsToString(params), e);
        }finally {
            DBUtil.closeCon(con);
        }
        return object;
    }

    private static String paramsToString(Object... params) {
        if (params == null) return null;
        StringBuffer sb = new StringBuffer();
        for (Object o : params)
            sb.append(o==null?"":o.toString()).append("|");
        return sb.toString();
    }
}
